<?php
/**
 * ランキングバッチコントローラー
 *
 * @package  app
 * @extends  Controller
 */
class Controller_Batch_Ranking extends Controller_Manage {
	/**
	 * 毎時
	 *
	 * @access  public
	 * @return  Response
	 */
	public function action_hourly() {
		$date = date("Y/m/d");

		// 本日初めての集計?
		$first = false;
		$query = DB::Select(DB::expr('COUNT(*) AS count'));
		$query -> from('ranking_daily');
		$query -> where('ranking_date', $date);
		$result = $query -> execute() -> as_array();
		if ($result[0]['count'] == 0) {
			$first = true;
		}

		// 楽曲DB 24時間
		DB::start_transaction();
		try {
			// 念のため、重複してたら削除
			$query = DB::delete('ranking_daily');
			$query -> where('ranking_date', $date);
			$query -> where('ranking_kind', 'in', array(0, 1, 2));
			$query -> execute();

			// INSERT文
			$sql = "INSERT INTO ranking_daily (ranking_kind, ranking_date, song_id, score)";
			$sql .= " SELECT %d,'" . $date . "',song_id,COUNT(*) AS score";
			$sql .= " FROM %s WHERE %s create_date > ( NOW( ) - INTERVAL 1 DAY ) GROUP BY song_id";

			// コメント数
			DB::query(sprintf($sql, 0, "comment", "del_flg=0 AND")) -> execute();

			// お気に入り
			DB::query(sprintf($sql, 1, "favorite", "")) -> execute();

			// 再生数
			DB::query(sprintf($sql, 2, "play", "")) -> execute();

			// コミット
			DB::commit_transaction();
		} catch (\Exception $e) {
			throw $e;
			$this -> alert($e);

			// ロールバック
			DB::rollback_transaction();
			return Response::forge('NG', D_HTTP_WARNING);
		}

		// ヒットチャート
		DB::start_transaction();
		try {
			// RSSを取得
			$itunes = $this -> get_itunesrss(Config::get('itunes_url_rss100'));
			
			if (count($itunes) > 0) {
				// 念のため、重複してたら削除
				$query = DB::delete('ranking_daily');
				$query -> where('ranking_date', $date);
				$query -> where('ranking_kind', 3);
				$query -> execute();

				// スコアを付ける
				for ($i = 0; $i < count($itunes); $i++) {
					$cols = $itunes[$i];

					$values = array();
					$values['ranking_kind'] = 3;
					$values['ranking_date'] = $date;
					$values['song_id'] = $cols['itunes_track_id'];
					$values['score'] = count($itunes) - $i;

					$query = DB::Insert('ranking_daily');
					$query -> set($values);
					$query -> execute();

					if ($first) {
						$query = DB::Update('itunes_song');
						$query -> where('itunes_track_id', $cols['itunes_track_id']);
						$query -> set(array('score_ranking' => DB::expr('score_ranking + ' . $values['score'])));
						$query -> execute();
					}
				}

				// コミット
				DB::commit_transaction();
			}
		} catch (\Exception $e) {
			throw $e;
			$this -> alert($e);

			// ロールバック
			DB::rollback_transaction();
			return Response::forge('NG', D_HTTP_WARNING);
		}

		return Response::forge('OK', D_HTTP_UPDATE);
	}

	/**
	 * 日時
	 *
	 * @access  public
	 * @return  Response
	 */
	public function action_daily() {
		$date = date("Y/m/d", time() - 60 * 60 * 24);

		// 本日初めての集計?
		$first = false;
		$query = DB::Select(DB::expr('COUNT(*) AS count'));
		$query -> from('ranking_total');
		$query -> where('ranking_date', $date);
		$result = $query -> execute() -> as_array();
		if ($result[0]['count'] == 0) {
			$first = true;
		}

		// 累計
		if ($first) {
			DB::start_transaction();
			try {
				// 念のため、重複してたら削除
				$query = DB::delete('ranking_total');
				$query -> where('ranking_date', $date);
				$query -> execute();

				// 曲DB
				$count = array('count_comment', 'count_favorite', 'count_play');
				for ($i = 0; $i < count($count); $i++) {
					$sql = "INSERT INTO ranking_total (ranking_kind, ranking_date, song_id, score)";
					$sql .= " SELECT '%s','%s',song_id,%s";
					$sql .= " FROM song";
					$sql .= " ORDER BY %s DESC LIMIT 100";
					$s = sprintf($sql, $i, $date, $count[$i], $count[$i]);
					DB::query($s) -> execute();
				}

				// iTunes
				$sql = "INSERT INTO ranking_total (ranking_kind, ranking_date, song_id, score)";
				$sql .= " SELECT '3','%s',itunes_track_id,score_ranking";
				$sql .= " FROM itunes_song";
				$sql .= " ORDER BY score_ranking DESC LIMIT 100";
				$s = sprintf($sql, $date);
				DB::query($s) -> execute();

				// コミット
				DB::commit_transaction();
			} catch (\Exception $e) {
				throw $e;
				$this -> alert($e);

				// ロールバック
				DB::rollback_transaction();
				return false;
			}
		}

		// 週間
		$start = date("Y/m/d", strtotime("Monday previous week"));
		$end = date("Y/m/d", strtotime("Sunday previous week"));
		if (!$this -> sum_daily("weekly", $start, $end)) {
			return Response::forge('NG', D_HTTP_WARNING);
		}

		// 月刊
		$start = date('Y/m/', mktime(0, 0, 0, date('m') - 1, 1, date('Y'))) . "01";
		$end = date('Y/m/d', mktime(0, 0, 0, date('m'), 0, date('Y')));
		if (!$this -> sum_daily("monthly", $start, $end)) {
			return Response::forge('NG', D_HTTP_WARNING);
		}

		return Response::forge('OK', D_HTTP_UPDATE);
	}

	public function sum_daily($target, $start, $end) {
		DB::start_transaction();
		try {
			// 念のため、重複してたら削除
			$query = DB::delete('ranking_' . $target);
			$query -> where('ranking_date', $start);
			$query -> execute();

			$sql = "INSERT INTO ranking_%s (ranking_kind, ranking_date, song_id, score)";
			$sql .= " SELECT ranking_kind, '%s',song_id,SUM(score)";
			$sql .= " FROM ranking_daily WHERE";
			$sql .= " ranking_date <= '%s' AND ranking_date >= '%s'";
			$sql .= " GROUP BY ranking_kind,song_id";
			$s = sprintf($sql, $target, $start, $end, $start);
			DB::query($s) -> execute();

			// コミット
			DB::commit_transaction();
		} catch (\Exception $e) {
			throw $e;
			$this -> alert($e);

			// ロールバック
			DB::rollback_transaction();
			return false;
		}

		return true;
	}

}
